/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Facility;
import java.sql.*;
/**
 *
 * @author mishra
 */
public class Query {
    static Connection con=null;
public static int add(FacilityBean fb)throws SQLException
{
    int i=0,k=0;
    try
    {
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("insert into facility values(facilityid.nextval,?,?,?,?,?)");
        pre.setInt(1, fb.getTransportationStatus());
        pre.setInt(2, fb.getHostelStatus());
        pre.setInt(3, fb.getMessStatus());
        pre.setInt(4, fb.getAdmissionId());
        pre.setString(5, fb.getEntryEmail());
       i= pre.executeUpdate();
       if(i!=0)
       {
           k=getMaxId();
       }
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{con.close();}
    return k;
}
public static int getMaxId() throws SQLException
{
    int i=0;
    try
    {
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("select max(facility_id) from facility");
        ResultSet rs=pre.executeQuery();
        if(rs.next())
        {
            i=rs.getInt(1);
        }
        
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{con.close();}
    return i;
}
public static int edit(FacilityBean fb)throws SQLException
{
    int i=0;
    try
    {
        con=DBConnection.Connection.getConnection();
        PreparedStatement pre=con.prepareStatement("update facility set TRANSPORTATION_STATUS=?,HOSTEL_STATUS=?,MESS_STATUS=?,ADMISSION_ID=?,ENTRY_EMAIL=? where FACILITY_ID=?");
        pre.setInt(1, fb.getTransportationStatus());
        pre.setInt(2, fb.getHostelStatus());
        pre.setInt(3, fb.getMessStatus());
        pre.setInt(4, fb.getAdmissionId());
        pre.setString(5, fb.getEntryEmail());
        pre.setInt(6, fb.getFacilityId());
       i= pre.executeUpdate();
       
    }catch(Exception e)
    {
        System.out.println(e);
    }finally{con.close();}
    return i;
}
 public static int chkFacility(int admissionId, int facilityId) throws SQLException {
        int i = 0;
        try {
            con = DBConnection.Connection.getConnection();
            PreparedStatement pre = con.prepareStatement("select * from facility where ADMISSION_ID=? or facility_id=?");
            pre.setInt(1, admissionId);
            pre.setInt(2, facilityId);
            ResultSet rs = pre.executeQuery();
            if (rs.next()) {
                i = 1;
            }
        } catch (Exception e) {
            System.out.println(e);
        } finally {
            con.close();
        }
        return i;
    }
}
